After completing this lesson, you’ll be able to:
Several transformers can join data by matching attribute values (keys). Some of these are more oriented towards geometry, while others have a more SQL-like style. Some join streams of data within one workspace, while others join one stream of data to an external database.
Which you use depends on your join requirements and performance needs.
The FeatureJoiner is another transformer for joining two streams of data within a workspace based on a key field match. It is configured using SQL-style joins and can often be more performant than the FeatureMerger.
Here, for example, is the same Roads/Snowfall match in the FeatureJoiner. The parameters for the transformer looks like this:
As you can see, this transformer is based more on traditional SQL queries. The Join Mode parameter can take one of three values:
| Mode | Description | Depiction | Joined Output | Unjoined Left | Unjoined Right |
|---|---|---|---|---|---|
| Left | Left features look for a match and are output whether they find a match or not |  |
All matches plus unmatched Left features | None | Unused Right features |
| Inner | Left features look for a match and are output if they find one |  |
All matches only | Unmatched Left features | Unused Right features |
| Full | Both Left and Right features output through the Joined output port, whether they find a join or not |  |
All matches plus unmatched Left and Right features | None | None |
Other terms you might be familiar with are outer join and right join. An outer join is simply a different name for what the full join does here. To do a right join, you would switch which features are being sent to which input port and use the left join option.
With a left join the user either believes that all roads will have a matching snowfall record, or it does not matter if there is not a match. In fact, no features will ever appear from the UnjoinedLeft output port.
If it was essential to ensure a match, then the chosen mode should be inner. Then records that exited the UnjoinedLeft output port could be treated as an error and investigated as to why there is no match.
There are parameters to handle conflicts of information and whether to merge attributes only or geometry as well.
The DatabaseJoiner transformer is different from the FeatureJoiner because instead of merging two streams of features, it merges one (or more) stream(s) of data with records from an external database.
Here is the same example as the FeatureMerger above. In this case, the roads features are obtaining snowfall data directly from a table in an Excel spreadsheet:
The parameters dialog for the DatabaseJoiner looks like this:
Again, StreetID is being used from both feature and database table to facilitate a merge between the two.
As with the other transformers, there are parameters to control the attributes that are accumulated and how conflicts are resolved.
The InlineQuerier transformer accepts features from the workspace and generates a temporary database. With that database it's possible to apply any SQL commands required – including joins – across a number of tables:
The InlineQuerier has the distinct advantage of allowing its input to be reused multiple times in a single transformer; whereas multiple joins would otherwise require multiple FeatureJoiner transformers. However, there is a performance overhead involved in generating that initial database.
